package qj.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

public class RdbmsUtil {

	public static long getMaxId(String idCol, String tableName, Class colType, Connection conn) throws SQLException {
		String sql;
		if (!colType.equals(String.class)) {
			sql = "select max(" + idCol + ") from " + tableName;
		} else {
			sql = "select max(TO_CHAR(" + idCol + ", '999999999999')) from " + tableName;
		}
		ResultSet rs = null;
		Statement ps = null;
		try {
			ps = conn.createStatement();
			rs = ps.executeQuery(sql);
			if (rs.next()) {
				return rs.getLong(1);
			} else {
				return 0L;
			}
		} finally {
			if (rs != null) {
				rs.close();
			}
			if (ps != null) {
				ps.close();
			}
		}
	}
	
	public static String genInserts(ResultSet rs, String tableName) throws SQLException {
		ResultSetMetaData metaData = rs.getMetaData();
		String columnNames = getColumnNames(metaData);
		int[] fieldTypes = getFieldTypes(metaData);
		StringBuffer sb = new StringBuffer();
		
		while (rs.next()) {
			sb.append("INSERT INTO " + tableName + "(" + columnNames);
			sb.append(") VALUES (");
			sb.append(getValues(rs, fieldTypes));
			sb.append(");\n");
		}
		return sb.toString();
	}

	private static Object getValues(ResultSet rs, int[] fieldTypes) throws SQLException {
		ResultSetMetaData metaData = rs.getMetaData();
		StringBuffer sb = new StringBuffer();
		for (int i = 0; i < metaData.getColumnCount(); i++) {
			if (i > 0)
				sb.append(", ");
			sb.append(getFormattedValue(rs, i, fieldTypes[i]));
		}
		return sb.toString();
	}

	public static final SimpleDateFormat DF_DATE = new SimpleDateFormat("dd MMM yyyy");
	public static final SimpleDateFormat DF_TIME = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
	private static String getFormattedValue(ResultSet rs, int i, int fieldType) throws SQLException {
		String value;

		switch (fieldType) {
		case Types.DATE:
			value = "'" + DF_DATE.format(rs.getDate(i + 1)) + "'";
			break;
		case Types.TIME:
		case Types.TIMESTAMP:
			value = "'" + DF_TIME.format(rs.getTime(i + 1)) + "'";
			break;
		default:
			value = "'" + rs.getString(i + 1) + "'";
		}
		
		if (!rs.wasNull()) {
			return value;
		} else {
			return "null";
		}
	}
	
	private static String getColumnNames(ResultSetMetaData metaData) throws SQLException {
		StringBuffer sb = new StringBuffer();
		for (int i = 0; i < metaData.getColumnCount(); i++) {
			if (i > 0)
				sb.append(", ");
			sb.append(metaData.getColumnName(i + 1));
		}
		return sb.toString();
	}
	
	public static int updateOrInsert(String tableName, List pKeys, List values, Connection conn) throws SQLException {
		return updateOrInsert(
				tableName, pKeys.toArray(), values.toArray(), conn
				);
	}
	/**
	 * 
	 * @param tableName
	 * @param pKeys
	 * @param values
	 * @param conn
	 * @return
	 * @throws SQLException
	 */
	public static int updateOrInsert(String tableName, Object[] pKeys, Object[] values, Connection conn) throws SQLException {
		String sqlUpdate = genSqlUpdate(tableName, pKeys, values);
		PreparedStatement psUpdate = null;
		PreparedStatement psInsert = null;
		try {
			psUpdate = conn.prepareStatement(sqlUpdate);
			
			setUpdateValues(psUpdate, pKeys, values);
			int ret = psUpdate.executeUpdate();
			if (ret == 0) {
				String sqlInsert = genSqlInsert(tableName, pKeys, values);
				
				psInsert = conn.prepareStatement(sqlInsert);
				
				setInsertValues(psInsert, pKeys, values);
				ret = psInsert.executeUpdate();
			}
			return ret;
		} finally {
			if (psUpdate!=null)
				psUpdate.close();
			if (psInsert!=null)
				psInsert.close();
		}		
	}

	/**
	 * 
	 * @param tableName
	 * @param keys
	 * @param values
	 * @return
	 */
	private static String genSqlUpdate(String tableName, Object[] keys, Object[] values) {
		StringBuffer sb = new StringBuffer();
		sb.append("UPDATE " + tableName);
		sb.append(" SET");
		for (int i = 0; i < values.length; i+=2) {
			if (i>0)
				sb.append(",");
			sb.append(" ").append(values[i]).append(" = ?");
		}
		sb.append(" WHERE");
		for (int i = 0; i < keys.length; i+=2) {
			if (i>0)
				sb.append(" AND");
			sb.append(" ").append(keys[i]).append(" = ?");
		}
//		System.out.println(sb);
		return sb.toString();
	}

	/**
	 * 
	 * @param tableName
	 * @param keys
	 * @param values
	 * @return
	 */
	private static String genSqlInsert(String tableName, Object[] keys, Object[] values) {
		StringBuffer sb = new StringBuffer();
		sb.append("INSERT INTO " + tableName);
		sb.append(" (");
		for (int i = 0; i < keys.length + values.length; i+=2) {
			if (i>0)
				sb.append(",");
			
			sb.append(i < keys.length ? keys[i] :
				values[i - keys.length]);
		}
		
		sb.append(") VALUES (");
		for (int i = 0; i < keys.length + values.length; i+=2) {
			if (i>0)
				sb.append(",");
			sb.append("?");
		}
		sb.append(")");
//		System.out.println(sb);
		return sb.toString();
	}
	
	/**
	 * 
	 * @param psUpdate
	 * @param keys
	 * @param values
	 * @throws SQLException
	 */
	private static void setUpdateValues(PreparedStatement psUpdate, Object[] keys, Object[] values) throws SQLException {
		for (int i = 0; i < values.length / 2; i++) {
			setPsValue(i + 1, values[i*2 + 1], psUpdate);
		}
		for (int i = 0; i < keys.length / 2; i++) {
			setPsValue(i + 1 + values.length / 2, keys[i*2 + 1], psUpdate);
		}
	}

	private static final DecimalFormat NF = new DecimalFormat("#.###########");
	/**
	 * Accept String, Date, Integer, Long, Double, Float, Boolean
	 * @param i
	 * @param object
	 * @param psUpdate
	 * @throws SQLException 
	 */
	public static void setPsValue(int i, Object o, PreparedStatement ps) throws SQLException {
		
		if (o == null) {
			ps.setString(i, null);
		} else if (o instanceof String) {
			ps.setString(i, (String) o);
		} else if (o instanceof Date) {
//			System.out.println(o);
			ps.setTimestamp(i, new java.sql.Timestamp(((Date) o).getTime()));
		} else if (o instanceof Integer) {
			ps.setInt(i, ((Integer) o).intValue());
		} else if (o instanceof Long) {
			ps.setLong(i, ((Long) o).longValue());
		} else if (o instanceof Double) {
			ps.setDouble(i, ((Double) o).doubleValue());
//			System.out.println(NF.format(((Double) o).doubleValue()));
//			ps.setString(i, NF.format(((Double) o).doubleValue()));
		} else if (o instanceof Float) {
			ps.setFloat(i, ((Float) o).floatValue());
		} else if (o instanceof Boolean) {
			ps.setBoolean(i, ((Boolean) o).booleanValue());
		} else {
			throw new IllegalArgumentException("This object class is not supported: " + o.getClass().getName() + ". Please use one of those classes:" +
					"String, Date, Integer, Long, Double, Float, Boolean");
		}
	}
	
	/**
	 * 
	 * @param psUpdate
	 * @param keys
	 * @param values
	 * @throws SQLException
	 */
	private static void setInsertValues(PreparedStatement psUpdate, Object[] keys, Object[] values) throws SQLException {
		for (int i = 0; i < keys.length / 2; i++) {
			setPsValue(i + 1, keys[i*2 + 1], psUpdate);
		}
		for (int i = 0; i < values.length / 2; i++) {
			setPsValue(i + 1 + keys.length / 2, values[i*2 + 1], psUpdate);
		}
	}

	/**
	 * 
	 * @param fieldName
	 * @param num
	 * @return
	 */
	public static String multiEqualOr(String fieldName, int num) {
		StringBuffer sb = new StringBuffer();
		for (int i = 0; i < num; i++) {
			if (i>0)
				sb.append(" OR ");
			sb.append(fieldName).append("=?");
		}
		return sb.toString();
	}

	/**
	 * Test the connection with given sql
	 * @param conn
	 * @param sql
	 * @return
	 */
	public static boolean testConn(Connection conn, String sql) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			rs.next();
			rs.getString(1);
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs!=null)
					rs.close();
				if (ps!=null)
					ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return false;
	}

	public static String[] getColNames(ResultSetMetaData meta)
			throws SQLException {
		int columnCount = meta.getColumnCount();
		String[] result = new String[columnCount];
		for (int i = 1; i <= columnCount; i++) {
			result[i - 1] = meta.getColumnLabel(i);
		}
		return result;
	}

	/**
	 * Select all table data
	 * @param tableName
	 * @param conn
	 * @return
	 * @throws SQLException
	 */
	public static ResultSet selectTableData(String tableName,
			Connection conn) throws SQLException {
		String sql = "SELECT * FROM " + tableName;
		PreparedStatement ps = null;
		ps = conn.prepareStatement(sql);
		return ps.executeQuery();
	}

	public static String genInserts(String tableName, Connection conn) throws SQLException {
		return genInserts(selectTableData(tableName, conn), tableName);
	}

	public static Object getValue(ResultSet rs, int fieldType, int colScale, int colPrecision, String fieldName) throws SQLException {
		if (rs==null) {
			return null;
		}
		
		Object value;
		switch (fieldType) {
		case Types.ARRAY:
			value = rs.getArray(fieldName);
			break;

		case Types.BLOB:
			value = rs.getBlob(fieldName);
			break;

		case Types.BOOLEAN:
			value = Boolean.valueOf(rs.getBoolean(fieldName));
			break;

		case Types.CLOB:
			value = rs.getClob(fieldName);
			break;

		case Types.DATE:
			value = rs.getDate(fieldName);
			break;

		case Types.FLOAT:
			value = Float.valueOf(rs.getFloat(fieldName));
			break;

		case Types.REF:
			value = rs.getRef(fieldName);
			break;

		case Types.TIME:
			value = rs.getTime(fieldName);
			break;

		case Types.TIMESTAMP:
			value = rs.getTimestamp(fieldName);
			break;

		case Types.BIGINT:
		case Types.SMALLINT:
		case Types.INTEGER:
		case Types.BINARY:
		case Types.BIT:
		case Types.TINYINT:
			value = Long.valueOf(rs.getLong(fieldName));
			break;

		case Types.NUMERIC:
		case Types.LONGVARBINARY:
		case Types.REAL:
		case Types.DECIMAL:
		case Types.VARBINARY:
		case Types.DOUBLE:
			if (colScale > 0) {
				value = Double.valueOf(rs.getDouble(fieldName));
			} else {
				value = Long.valueOf(rs.getLong(fieldName));
			}
			break;

		case Types.DISTINCT:
		case Types.STRUCT:
		case Types.LONGVARCHAR:
		case Types.CHAR:
		case Types.DATALINK:
		case Types.VARCHAR:
		default:
			value = rs.getString(fieldName);
			break;
		}
		if (rs.wasNull()) {
			return null;
		} else {
			return value;
		}
	}
	
	/**
	 * TODO Should be using getValue()
	 * @param ps
	 * @param rs
	 * @param i
	 * @param fieldType
	 * @param fieldName
	 * @throws SQLException
	 */
	public static void populateStatement(PreparedStatement ps, ResultSet rs, int i, int fieldType, String fieldName) throws SQLException {
		switch (fieldType) {
		case Types.ARRAY:
			ps.setArray(i + 1, rs.getArray(fieldName));
			break;
	
		case Types.BLOB:
			ps.setBlob(i + 1, rs.getBlob(fieldName));
			break;
	
		case Types.BOOLEAN:
			ps.setBoolean(i + 1, rs.getBoolean(fieldName));
			break;
	
		case Types.CLOB:
			ps.setClob(i + 1, rs.getClob(fieldName));
			break;
	
		case Types.DATE:
			ps.setDate(i + 1, rs.getDate(fieldName));
			break;
	
		case Types.FLOAT:
			ps.setFloat(i + 1, rs.getFloat(fieldName));
			break;
			
		case Types.REF:
			ps.setRef(i + 1, rs.getRef(fieldName));
			break;
	
		case Types.TIME:
			ps.setTime(i + 1, rs.getTime(fieldName));
			break;
	
		case Types.TIMESTAMP:
			ps.setTimestamp(i + 1, rs.getTimestamp(fieldName));
			break;
	
		case Types.BIGINT:
		case Types.SMALLINT:
		case Types.INTEGER:
		case Types.BINARY:
		case Types.BIT:
		case Types.TINYINT:
			ps.setInt(i + 1, rs.getInt(fieldName));
			break;
	
		case Types.NUMERIC:
		case Types.LONGVARBINARY:
		case Types.REAL:
		case Types.DECIMAL:
		case Types.VARBINARY:
		case Types.DOUBLE:
			ps.setDouble(i + 1, rs.getDouble(fieldName));
			break;
	
		case Types.DISTINCT:
		case Types.STRUCT:
		case Types.LONGVARCHAR:
		case Types.CHAR:
		case Types.DATALINK:
		case Types.VARCHAR:
		default:
			ps.setString(i + 1, rs.getString(fieldName));
			break;
		}
		if (rs.wasNull())
			ps.setNull(i + 1, fieldType);
	}

	public static int[] getFieldTypes(ResultSetMetaData meta)
			throws SQLException {
		int columnCount = meta.getColumnCount();
		int[] result = new int[meta.getColumnCount()];
		for (int i = 1; i <= columnCount; i++) {
			result[i - 1] = meta.getColumnType(i);
		}
		return result;
	}

	public static void populateStatement(String[] fieldNames, int[] fieldTypes,
			ResultSet rs, PreparedStatement ps) throws SQLException {
		for (int i = 0; i < fieldNames.length; i++) {
			int fieldType = fieldTypes[i];
			String fieldName = fieldNames[i];
			populateStatement(ps, rs, i, fieldType, fieldName);
		}
	}

	public static String makePreparedInsertSQL(String[] fieldNames, String tableName) {
		StringBuffer sb = new StringBuffer("INSERT INTO " + tableName + "(");
		int size = fieldNames.length;
		for (int i = 0; i < size; i++) {
			if (i > 0)
				sb.append(", ");
			sb.append(fieldNames[i]);
		}
		sb.append(") VALUES (").append(getInsertPlaceHolder(size)).append(")");
		return sb.toString();
	}

	public static String getInsertPlaceHolder(int count) {
		StringBuffer sb = new StringBuffer();
		for (int i = 0; i < count; i++) {
			if (i > 0)
				sb.append(", ");
			sb.append("?");
		}
		String result = sb.toString();
		return result;
	}

	public static int[] getColPrecisions(ResultSetMetaData meta) throws SQLException {
		int columnCount = meta.getColumnCount();
		int[] result = new int[meta.getColumnCount()];
		for (int i = 1; i <= columnCount; i++) {
			result[i - 1] = meta.getPrecision(i);
		}
		return result;
	}

	public static int[] getColScales(ResultSetMetaData meta) throws SQLException {
		int columnCount = meta.getColumnCount();
		int[] result = new int[meta.getColumnCount()];
		for (int i = 1; i <= columnCount; i++) {
			result[i - 1] = meta.getScale(i);
		}
		return result;
	}
}
